package com.imut.javabean;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.imut.common.ConnectionFactory;
import com.imut.common.Page;
import com.imut.common.ResourceClose;

public class PostDBAccess {
	public void addPost(Post post){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="insert into Post values(?,?,?,?,?,?,?,?,?)"; 
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, post.getPostNo());
			pstmt.setString(2, post.getPostName());
			pstmt.setString(3, post.getPassword());
			pstmt.setInt(4, post.getProfessional());
			pstmt.setString(5,post.getEducation());
			pstmt.setString(6, post.getAddress());
			pstmt.setString(7, post.getPhone());
			pstmt.setString(8, post.getEmail());
			pstmt.setString(9, post.getSubject());
			pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	public void delPost(String PostNo){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="delete from Post where PostNo=?"; 
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, PostNo);
			pstmt.executeUpdate();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	public void updatePost(Post post){
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=ConnectionFactory.getConnection();
			//待修改	
		String sql="update Post set PostNo=?,PostName=?,password=?,professional=?,"+ 
				"education=?,address=?,phone=?,email=?,subject=? where PostNo=?";       
		//待修改	
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, post.getPostNo());
			pstmt.setString(2, post.getPostName());
			pstmt.setString(3, post.getPassword());
			pstmt.setInt(4, post.getProfessional());
			pstmt.setString(5, post.getEducation());
			pstmt.setString(6, post.getAddress());
			pstmt.setString(7, post.getPhone());
			pstmt.setString(8, post.getEmail());
			pstmt.setString(9, post.getSubject());
			pstmt.setString(10, post.getPostNo());
			pstmt.executeUpdate();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			ResourceClose.close(rs, pstmt, conn);
		}
	}

	public Post findPostByPostNo(String postNo){
		Post Post=null;
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="select * from Post where PostNo=?"; 
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, postNo);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Post=new Post();
				Post.setPostNo(rs.getString(1));
				Post.setPostName(rs.getString(2));
				Post.setPassword(rs.getString(3));
				Post.setProfessional(rs.getInt(4));
				Post.setEducation(rs.getString(5));
				Post.setAddress(rs.getString(6));
				Post.setPhone(rs.getString(7));
				Post.setEmail(rs.getString(8));
				Post.setSubject(rs.getString(9));
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			ResourceClose.close(rs, pstmt, conn);
		}
		return Post;
	}

	public Map findAllPost(int curPage){
		Post Post=null;
		ArrayList list=new ArrayList();
		Connection conn=null;
		Statement pstmt=null;
		ResultSet rs=null;
		ResultSet r=null;
		Map map=null;
		Page pa=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="select * from Post order by PostNo"; 
			pstmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
			rs=pstmt.executeQuery(sql);
			pa=new Page();
			pa.setPageSize(5);
			pa.setPageCount(rs);
			pa.setCurPage(curPage);
			r=pa.setRs(rs);
			r.previous();
			for(int i=0;i<pa.getPageSize();i++){
				if(r.next()){
					Post=new Post();
					Post.setPostNo(r.getString(1));
					Post.setPostName(r.getString(2));
					Post.setPassword(rs.getString(3));
					Post.setProfessional(r.getInt(4));
					Post.setEducation(r.getString(5));
					Post.setAddress(r.getString(6));
					Post.setPhone(r.getString(7));
					Post.setEmail(r.getString(8));
					Post.setSubject(r.getString(9));
					list.add(Post);
				}else{
					break;
				}
			}
			map=new HashMap();
			map.put("list",list);
			map.put("pa",pa);
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			ResourceClose.close(rs, pstmt, conn);
			ResourceClose.close(r, null, null);
		}
		return map;
	}	
	
	/////ch9添加
	//列表显示所有教师列表
			public List findAllPost(){
				Post Post=null;
				ArrayList list=new ArrayList();
				Connection conn=null;
				Statement pstmt=null;
				ResultSet rs=null;
				try{
					conn=ConnectionFactory.getConnection();
					String sql="select * from Post order by PostNo"; 
					pstmt=conn.createStatement();
					rs=pstmt.executeQuery(sql);
					while(rs.next()){
						Post=new Post();
						Post.setPostNo(rs.getString(1));
						Post.setPostName(rs.getString(2));
						Post.setPassword(rs.getString(3));
						Post.setProfessional(rs.getInt(4));
						Post.setEducation(rs.getString(5));
						Post.setAddress(rs.getString(6));
						Post.setPhone(rs.getString(7));
						Post.setEmail(rs.getString(8));
						Post.setSubject(rs.getString(9));
						list.add(Post);
					}
				}catch (SQLException e) {
					e.printStackTrace();
				}finally{
					ResourceClose.close(rs, pstmt, conn);
				}
				return list;
			}
	

	public List findAllPostByMostCon(String PostNo,String PostName,
			Integer professional,String phone,String subject){
		Post Post=null;
		ArrayList list=new ArrayList();
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		
		
		String sql="select * from Post where 1=1 ";		
		
		if(PostNo!=null&&!PostNo.equals("")){
			sql+=" and PostNo like '%"+PostNo+"%'";
		}
		if(PostName!=null&&!PostName.equals("")){
			sql+=" and PostName like '%"+PostName+"%'";
		}
		if(phone!=null&&!phone.equals("")){
			sql+=" and phone like '%"+phone+"%'";
		}
		if(subject!=null&&!subject.equals("")){
			sql+=" and subject like '%"+subject+"%'";
		}
		if(professional!=null&&!professional.equals("")){
			sql+=" and professional="+professional;
		}		
		sql+=" order by PostNo";
		try{
			conn=ConnectionFactory.getConnection();
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Post=new Post();
				Post.setPostNo(rs.getString(1));
				Post.setPostName(rs.getString(2));
				Post.setPassword(rs.getString(3));
				Post.setProfessional(rs.getInt(4));
				Post.setEducation(rs.getString(5));
				Post.setAddress(rs.getString(6));
				Post.setPhone(rs.getString(7));
				Post.setEmail(rs.getString(8));
				Post.setSubject(rs.getString(9));
				list.add(Post);
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			ResourceClose.close(rs, pstmt, conn);
		}
		return list;
	}
	public Post login(String loginName, String password) {
		Post Post=null;
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			conn=ConnectionFactory.getConnection();
			String sql="select * from Post where PostNo=? and password=?"; 
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, loginName);
			pstmt.setString(2, password);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Post=new Post();
				Post.setPostNo(rs.getString(1));
				Post.setPostName(rs.getString(2));
				Post.setPassword(rs.getString(3));
			}
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			ResourceClose.close(rs, pstmt, conn);
		}
		return Post;
	}
}

